/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import workforce.ui.*;

/**
 *
 * @author TEComp
 */
public class PageService {

    private Connection conn = null;
    
    public void setConnection(Connection conn) {
        this.conn = conn;
    }
    
    public void close() {
        try {
            if( conn != null ) {
                conn.close();
            }
        } catch(SQLException ex) {
            ex.printStackTrace();
        } finally {
            conn = null;
        }
    }

    public UIPage findPageById(int id) {
        UIPage uiPage = new UIPage();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT PAGE_ID, PAGE_NAME FROM obj_web_page WHERE PAGE_ID = " + id + "");

            if (rs.next()) {
                int vid = rs.getInt("PAGE_ID");
                String name = rs.getString("PAGE_NAME");
                uiPage.setName(id + "| " + name);
                uiPage.setBody(findFormByPageId(vid));
            }
            rs.close();
            stmt.close();
            return uiPage;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    public Iterator<UIWeb> findFormByPageId(int pageId) {
         List<UIWeb> list = new ArrayList<UIWeb>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT FORM_ID, FORM_NAME FROM obj_web_form WHERE PAGE_ID = " + pageId + "");
            while (rs.next()) {
                int fid = rs.getInt("FORM_ID");
                String name = rs.getString("FORM_NAME");
                UIForm form = new UIForm();
                form.setId(fid);
                form.setName(name);
                Iterator<UIWeb> iterator = findFieldByFormId(fid);
                form.setBody(iterator);
                list.add(form);
            }
            rs.close();
            stmt.close();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    public Iterator<UIWeb> findFieldByFormId(int formId) {
          List<UIWeb> list = new ArrayList<UIWeb>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT FIELD_ID, FIELD_NAME, FIELD_TYPE FROM obj_web_field WHERE FORM_ID = " + formId + "");
            while (rs.next()) {
                int fid = rs.getInt("FIELD_ID");
                int type = rs.getInt("FIELD_TYPE");
                String name = rs.getString("FIELD_NAME");
                UIWeb field = null;
                switch( type ) {
                    case 1:
                        field = new UIText();
                        UIText text = (UIText)field;
                        text.setId(fid);
                        text.setName(name);
                        Iterator<UIAttribute> iterator = findAttributeByFieldId(fid);
                        text.setAttrs(iterator);
                        break;
                }
                list.add(field);
            }
            rs.close();
            stmt.close();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    public Iterator<UIAttribute> findAttributeByFieldId(int fieldId) {
          List<UIAttribute> list = new ArrayList<UIAttribute>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT ATTR_NAME, ATTR_VALUE FROM obj_web_attr WHERE FIELD_ID = " + fieldId + "");
            while (rs.next()) {
                String name = rs.getString("ATTR_NAME");
                String value = rs.getString("ATTR_VALUE");
                UIAttribute attr = new UIAttribute();
                attr.setKey(name);
                attr.setValue(value);
                list.add(attr);
            }
            rs.close();
            stmt.close();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }
    
    
}